{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "import MySQLdb as mdb\n",
    "\n",
    "import matplotlib\n",
    "matplotlib.use('TkAgg')\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from matplotlib.collections import LineCollection\n",
    "\n",
    "from sklearn import cluster,covariance,manifold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api(\"b8feb479c2c56b41ec975661c9f71b88f025e962e7f4e96e2339a6d9\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5395 entries, 0 to 5394\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   ts_code       5395 non-null   object\n",
      " 1   symbol        5395 non-null   object\n",
      " 2   name          5395 non-null   object\n",
      " 3   area          5380 non-null   object\n",
      " 4   industry      5380 non-null   object\n",
      " 5   cnspell       5395 non-null   object\n",
      " 6   market        5395 non-null   object\n",
      " 7   list_date     5395 non-null   object\n",
      " 8   act_name      2575 non-null   object\n",
      " 9   act_ent_type  2575 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 421.6+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ts_code         5395\n",
       "symbol          5395\n",
       "name            5395\n",
       "area            5380\n",
       "industry        5380\n",
       "cnspell         5395\n",
       "market          5395\n",
       "list_date       5395\n",
       "act_name        2575\n",
       "act_ent_type    2575\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pro.query(\"stock_basic\", exchange='', list_status='L')\n",
    "data.info()\n",
    "data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5395"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('mysql+pymysql://root:zgz200556@localhost:3306/stock_basic')\n",
    "data.to_sql('stock_basic', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['000001.SZ',\n",
       " '000002.SZ',\n",
       " '000004.SZ',\n",
       " '000006.SZ',\n",
       " '000007.SZ',\n",
       " '000008.SZ',\n",
       " '000009.SZ',\n",
       " '000010.SZ',\n",
       " '000011.SZ',\n",
       " '000012.SZ',\n",
       " '000014.SZ',\n",
       " '000016.SZ',\n",
       " '000017.SZ',\n",
       " '000019.SZ',\n",
       " '000020.SZ',\n",
       " '000021.SZ',\n",
       " '000025.SZ',\n",
       " '000026.SZ',\n",
       " '000027.SZ',\n",
       " '000028.SZ',\n",
       " '000029.SZ',\n",
       " '000030.SZ',\n",
       " '000031.SZ',\n",
       " '000032.SZ',\n",
       " '000034.SZ',\n",
       " '000035.SZ',\n",
       " '000036.SZ',\n",
       " '000037.SZ',\n",
       " '000039.SZ',\n",
       " '000040.SZ',\n",
       " '000042.SZ',\n",
       " '000045.SZ',\n",
       " '000048.SZ',\n",
       " '000049.SZ',\n",
       " '000050.SZ',\n",
       " '000055.SZ',\n",
       " '000056.SZ',\n",
       " '000058.SZ',\n",
       " '000059.SZ',\n",
       " '000060.SZ',\n",
       " '000061.SZ',\n",
       " '000062.SZ',\n",
       " '000063.SZ',\n",
       " '000065.SZ',\n",
       " '000066.SZ',\n",
       " '000068.SZ',\n",
       " '000069.SZ',\n",
       " '000070.SZ',\n",
       " '000078.SZ',\n",
       " '000088.SZ',\n",
       " '000089.SZ',\n",
       " '000090.SZ',\n",
       " '000096.SZ',\n",
       " '000099.SZ',\n",
       " '000100.SZ',\n",
       " '000151.SZ',\n",
       " '000153.SZ',\n",
       " '000155.SZ',\n",
       " '000156.SZ',\n",
       " '000157.SZ',\n",
       " '000158.SZ',\n",
       " '000159.SZ',\n",
       " '000166.SZ',\n",
       " '000301.SZ',\n",
       " '000333.SZ',\n",
       " '000338.SZ',\n",
       " '000400.SZ',\n",
       " '000401.SZ',\n",
       " '000402.SZ',\n",
       " '000403.SZ',\n",
       " '000404.SZ',\n",
       " '000407.SZ',\n",
       " '000408.SZ',\n",
       " '000409.SZ',\n",
       " '000410.SZ',\n",
       " '000411.SZ',\n",
       " '000415.SZ',\n",
       " '000417.SZ',\n",
       " '000419.SZ',\n",
       " '000420.SZ',\n",
       " '000421.SZ',\n",
       " '000422.SZ',\n",
       " '000423.SZ',\n",
       " '000425.SZ',\n",
       " '000426.SZ',\n",
       " '000428.SZ',\n",
       " '000429.SZ',\n",
       " '000430.SZ',\n",
       " '000488.SZ',\n",
       " '000498.SZ',\n",
       " '000501.SZ',\n",
       " '000503.SZ',\n",
       " '000504.SZ',\n",
       " '000505.SZ',\n",
       " '000506.SZ',\n",
       " '000507.SZ',\n",
       " '000509.SZ',\n",
       " '000510.SZ',\n",
       " '000513.SZ',\n",
       " '000514.SZ',\n",
       " '000516.SZ',\n",
       " '000517.SZ',\n",
       " '000518.SZ',\n",
       " '000519.SZ',\n",
       " '000520.SZ',\n",
       " '000521.SZ',\n",
       " '000523.SZ',\n",
       " '000524.SZ',\n",
       " '000525.SZ',\n",
       " '000526.SZ',\n",
       " '000528.SZ',\n",
       " '000529.SZ',\n",
       " '000530.SZ',\n",
       " '000531.SZ',\n",
       " '000532.SZ',\n",
       " '000533.SZ',\n",
       " '000534.SZ',\n",
       " '000536.SZ',\n",
       " '000537.SZ',\n",
       " '000538.SZ',\n",
       " '000539.SZ',\n",
       " '000541.SZ',\n",
       " '000543.SZ',\n",
       " '000544.SZ',\n",
       " '000545.SZ',\n",
       " '000546.SZ',\n",
       " '000547.SZ',\n",
       " '000548.SZ',\n",
       " '000550.SZ',\n",
       " '000551.SZ',\n",
       " '000552.SZ',\n",
       " '000553.SZ',\n",
       " '000554.SZ',\n",
       " '000555.SZ',\n",
       " '000557.SZ',\n",
       " '000558.SZ',\n",
       " '000559.SZ',\n",
       " '000560.SZ',\n",
       " '000561.SZ',\n",
       " '000563.SZ',\n",
       " '000564.SZ',\n",
       " '000565.SZ',\n",
       " '000566.SZ',\n",
       " '000567.SZ',\n",
       " '000568.SZ',\n",
       " '000570.SZ',\n",
       " '000571.SZ',\n",
       " '000572.SZ',\n",
       " '000573.SZ',\n",
       " '000576.SZ',\n",
       " '000581.SZ',\n",
       " '000582.SZ',\n",
       " '000584.SZ',\n",
       " '000586.SZ',\n",
       " '000589.SZ',\n",
       " '000590.SZ',\n",
       " '000591.SZ',\n",
       " '000592.SZ',\n",
       " '000593.SZ',\n",
       " '000595.SZ',\n",
       " '000596.SZ',\n",
       " '000597.SZ',\n",
       " '000598.SZ',\n",
       " '000599.SZ',\n",
       " '000600.SZ',\n",
       " '000601.SZ',\n",
       " '000603.SZ',\n",
       " '000605.SZ',\n",
       " '000607.SZ',\n",
       " '000608.SZ',\n",
       " '000609.SZ',\n",
       " '000610.SZ',\n",
       " '000612.SZ',\n",
       " '000615.SZ',\n",
       " '000617.SZ',\n",
       " '000619.SZ',\n",
       " '000620.SZ',\n",
       " '000622.SZ',\n",
       " '000623.SZ',\n",
       " '000625.SZ',\n",
       " '000626.SZ',\n",
       " '000627.SZ',\n",
       " '000628.SZ',\n",
       " '000629.SZ',\n",
       " '000630.SZ',\n",
       " '000631.SZ',\n",
       " '000632.SZ',\n",
       " '000633.SZ',\n",
       " '000635.SZ',\n",
       " '000636.SZ',\n",
       " '000637.SZ',\n",
       " '000638.SZ',\n",
       " '000639.SZ',\n",
       " '000650.SZ',\n",
       " '000651.SZ',\n",
       " '000652.SZ',\n",
       " '000655.SZ',\n",
       " '000656.SZ',\n",
       " '000657.SZ',\n",
       " '000659.SZ',\n",
       " '000661.SZ',\n",
       " '000663.SZ',\n",
       " '000665.SZ',\n",
       " '000668.SZ',\n",
       " '000669.SZ',\n",
       " '000670.SZ',\n",
       " '000672.SZ',\n",
       " '000676.SZ',\n",
       " '000677.SZ',\n",
       " '000678.SZ',\n",
       " '000679.SZ',\n",
       " '000680.SZ',\n",
       " '000681.SZ',\n",
       " '000682.SZ',\n",
       " '000683.SZ',\n",
       " '000685.SZ',\n",
       " '000686.SZ',\n",
       " '000688.SZ',\n",
       " '000690.SZ',\n",
       " '000691.SZ',\n",
       " '000692.SZ',\n",
       " '000695.SZ',\n",
       " '000697.SZ',\n",
       " '000698.SZ',\n",
       " '000700.SZ',\n",
       " '000701.SZ',\n",
       " '000702.SZ',\n",
       " '000703.SZ',\n",
       " '000705.SZ',\n",
       " '000707.SZ',\n",
       " '000708.SZ',\n",
       " '000709.SZ',\n",
       " '000710.SZ',\n",
       " '000711.SZ',\n",
       " '000712.SZ',\n",
       " '000713.SZ',\n",
       " '000715.SZ',\n",
       " '000716.SZ',\n",
       " '000717.SZ',\n",
       " '000718.SZ',\n",
       " '000719.SZ',\n",
       " '000720.SZ',\n",
       " '000721.SZ',\n",
       " '000722.SZ',\n",
       " '000723.SZ',\n",
       " '000725.SZ',\n",
       " '000726.SZ',\n",
       " '000727.SZ',\n",
       " '000728.SZ',\n",
       " '000729.SZ',\n",
       " '000731.SZ',\n",
       " '000733.SZ',\n",
       " '000735.SZ',\n",
       " '000736.SZ',\n",
       " '000737.SZ',\n",
       " '000738.SZ',\n",
       " '000739.SZ',\n",
       " '000750.SZ',\n",
       " '000751.SZ',\n",
       " '000752.SZ',\n",
       " '000753.SZ',\n",
       " '000755.SZ',\n",
       " '000756.SZ',\n",
       " '000757.SZ',\n",
       " '000758.SZ',\n",
       " '000759.SZ',\n",
       " '000761.SZ',\n",
       " '000762.SZ',\n",
       " '000766.SZ',\n",
       " '000767.SZ',\n",
       " '000768.SZ',\n",
       " '000776.SZ',\n",
       " '000777.SZ',\n",
       " '000778.SZ',\n",
       " '000779.SZ',\n",
       " '000782.SZ',\n",
       " '000783.SZ',\n",
       " '000785.SZ',\n",
       " '000786.SZ',\n",
       " '000788.SZ',\n",
       " '000789.SZ',\n",
       " '000790.SZ',\n",
       " '000791.SZ',\n",
       " '000792.SZ',\n",
       " '000793.SZ',\n",
       " '000795.SZ',\n",
       " '000796.SZ',\n",
       " '000797.SZ',\n",
       " '000798.SZ',\n",
       " '000799.SZ',\n",
       " '000800.SZ',\n",
       " '000801.SZ',\n",
       " '000802.SZ',\n",
       " '000803.SZ',\n",
       " '000807.SZ',\n",
       " '000809.SZ',\n",
       " '000810.SZ',\n",
       " '000811.SZ',\n",
       " '000812.SZ',\n",
       " '000813.SZ',\n",
       " '000815.SZ',\n",
       " '000816.SZ',\n",
       " '000818.SZ',\n",
       " '000819.SZ',\n",
       " '000820.SZ',\n",
       " '000821.SZ',\n",
       " '000822.SZ',\n",
       " '000823.SZ',\n",
       " '000825.SZ',\n",
       " '000826.SZ',\n",
       " '000828.SZ',\n",
       " '000829.SZ',\n",
       " '000830.SZ',\n",
       " '000831.SZ',\n",
       " '000833.SZ',\n",
       " '000837.SZ',\n",
       " '000838.SZ',\n",
       " '000839.SZ',\n",
       " '000848.SZ',\n",
       " '000850.SZ',\n",
       " '000851.SZ',\n",
       " '000852.SZ',\n",
       " '000856.SZ',\n",
       " '000858.SZ',\n",
       " '000859.SZ',\n",
       " '000860.SZ',\n",
       " '000862.SZ',\n",
       " '000863.SZ',\n",
       " '000868.SZ',\n",
       " '000869.SZ',\n",
       " '000875.SZ',\n",
       " '000876.SZ',\n",
       " '000877.SZ',\n",
       " '000878.SZ',\n",
       " '000880.SZ',\n",
       " '000881.SZ',\n",
       " '000882.SZ',\n",
       " '000883.SZ',\n",
       " '000885.SZ',\n",
       " '000886.SZ',\n",
       " '000887.SZ',\n",
       " '000888.SZ',\n",
       " '000889.SZ',\n",
       " '000890.SZ',\n",
       " '000892.SZ',\n",
       " '000893.SZ',\n",
       " '000895.SZ',\n",
       " '000897.SZ',\n",
       " '000898.SZ',\n",
       " '000899.SZ',\n",
       " '000900.SZ',\n",
       " '000901.SZ',\n",
       " '000902.SZ',\n",
       " '000903.SZ',\n",
       " '000905.SZ',\n",
       " '000906.SZ',\n",
       " '000908.SZ',\n",
       " '000909.SZ',\n",
       " '000910.SZ',\n",
       " '000911.SZ',\n",
       " '000912.SZ',\n",
       " '000913.SZ',\n",
       " '000915.SZ',\n",
       " '000917.SZ',\n",
       " '000919.SZ',\n",
       " '000920.SZ',\n",
       " '000921.SZ',\n",
       " '000922.SZ',\n",
       " '000923.SZ',\n",
       " '000925.SZ',\n",
       " '000926.SZ',\n",
       " '000927.SZ',\n",
       " '000928.SZ',\n",
       " '000929.SZ',\n",
       " '000930.SZ',\n",
       " '000931.SZ',\n",
       " '000932.SZ',\n",
       " '000933.SZ',\n",
       " '000935.SZ',\n",
       " '000936.SZ',\n",
       " '000937.SZ',\n",
       " '000938.SZ',\n",
       " '000948.SZ',\n",
       " '000949.SZ',\n",
       " '000950.SZ',\n",
       " '000951.SZ',\n",
       " '000952.SZ',\n",
       " '000953.SZ',\n",
       " '000955.SZ',\n",
       " '000957.SZ',\n",
       " '000958.SZ',\n",
       " '000959.SZ',\n",
       " '000960.SZ',\n",
       " '000962.SZ',\n",
       " '000963.SZ',\n",
       " '000965.SZ',\n",
       " '000966.SZ',\n",
       " '000967.SZ',\n",
       " '000968.SZ',\n",
       " '000969.SZ',\n",
       " '000970.SZ',\n",
       " '000972.SZ',\n",
       " '000973.SZ',\n",
       " '000975.SZ',\n",
       " '000977.SZ',\n",
       " '000978.SZ',\n",
       " '000980.SZ',\n",
       " '000981.SZ',\n",
       " '000983.SZ',\n",
       " '000985.SZ',\n",
       " '000987.SZ',\n",
       " '000988.SZ',\n",
       " '000989.SZ',\n",
       " '000990.SZ',\n",
       " '000993.SZ',\n",
       " '000995.SZ',\n",
       " '000997.SZ',\n",
       " '000998.SZ',\n",
       " '000999.SZ',\n",
       " '001201.SZ',\n",
       " '001202.SZ',\n",
       " '001203.SZ',\n",
       " '001205.SZ',\n",
       " '001206.SZ',\n",
       " '001207.SZ',\n",
       " '001208.SZ',\n",
       " '001209.SZ',\n",
       " '001210.SZ',\n",
       " '001211.SZ',\n",
       " '001212.SZ',\n",
       " '001213.SZ',\n",
       " '001215.SZ',\n",
       " '001216.SZ',\n",
       " '001217.SZ',\n",
       " '001218.SZ',\n",
       " '001219.SZ',\n",
       " '001222.SZ',\n",
       " '001223.SZ',\n",
       " '001225.SZ',\n",
       " '001226.SZ',\n",
       " '001227.SZ',\n",
       " '001228.SZ',\n",
       " '001229.SZ',\n",
       " '001230.SZ',\n",
       " '001231.SZ',\n",
       " '001234.SZ',\n",
       " '001236.SZ',\n",
       " '001238.SZ',\n",
       " '001239.SZ',\n",
       " '001255.SZ',\n",
       " '001256.SZ',\n",
       " '001258.SZ',\n",
       " '001259.SZ',\n",
       " '001260.SZ',\n",
       " '001266.SZ',\n",
       " '001267.SZ',\n",
       " '001268.SZ',\n",
       " '001269.SZ',\n",
       " '001270.SZ',\n",
       " '001277.SZ',\n",
       " '001278.SZ',\n",
       " '001279.SZ',\n",
       " '001282.SZ',\n",
       " '001283.SZ',\n",
       " '001286.SZ',\n",
       " '001287.SZ',\n",
       " '001288.SZ',\n",
       " '001289.SZ',\n",
       " '001296.SZ',\n",
       " '001298.SZ',\n",
       " '001299.SZ',\n",
       " '001300.SZ',\n",
       " '001301.SZ',\n",
       " '001306.SZ',\n",
       " '001308.SZ',\n",
       " '001309.SZ',\n",
       " '001311.SZ',\n",
       " '001313.SZ',\n",
       " '001314.SZ',\n",
       " '001316.SZ',\n",
       " '001317.SZ',\n",
       " '001318.SZ',\n",
       " '001319.SZ',\n",
       " '001322.SZ',\n",
       " '001323.SZ',\n",
       " '001324.SZ',\n",
       " '001326.SZ',\n",
       " '001328.SZ',\n",
       " '001330.SZ',\n",
       " '001331.SZ',\n",
       " '001332.SZ',\n",
       " '001333.SZ',\n",
       " '001336.SZ',\n",
       " '001337.SZ',\n",
       " '001338.SZ',\n",
       " '001339.SZ',\n",
       " '001356.SZ',\n",
       " '001358.SZ',\n",
       " '001359.SZ',\n",
       " '001360.SZ',\n",
       " '001366.SZ',\n",
       " '001367.SZ',\n",
       " '001368.SZ',\n",
       " '001373.SZ',\n",
       " '001376.SZ',\n",
       " '001378.SZ',\n",
       " '001379.SZ',\n",
       " '001380.SZ',\n",
       " '001387.SZ',\n",
       " '001389.SZ',\n",
       " '001391.SZ',\n",
       " '001395.SZ',\n",
       " '001696.SZ',\n",
       " '001872.SZ',\n",
       " '001896.SZ',\n",
       " '001914.SZ',\n",
       " '001965.SZ',\n",
       " '001979.SZ',\n",
       " '002001.SZ',\n",
       " '002003.SZ',\n",
       " '002004.SZ',\n",
       " '002005.SZ',\n",
       " '002006.SZ',\n",
       " '002007.SZ',\n",
       " '002008.SZ',\n",
       " '002009.SZ',\n",
       " '002010.SZ',\n",
       " '002011.SZ',\n",
       " '002012.SZ',\n",
       " '002014.SZ',\n",
       " '002015.SZ',\n",
       " '002016.SZ',\n",
       " '002017.SZ',\n",
       " '002019.SZ',\n",
       " '002020.SZ',\n",
       " '002021.SZ',\n",
       " '002022.SZ',\n",
       " '002023.SZ',\n",
       " '002024.SZ',\n",
       " '002025.SZ',\n",
       " '002026.SZ',\n",
       " '002027.SZ',\n",
       " '002028.SZ',\n",
       " '002029.SZ',\n",
       " '002030.SZ',\n",
       " '002031.SZ',\n",
       " '002032.SZ',\n",
       " '002033.SZ',\n",
       " '002034.SZ',\n",
       " '002035.SZ',\n",
       " '002036.SZ',\n",
       " '002037.SZ',\n",
       " '002038.SZ',\n",
       " '002039.SZ',\n",
       " '002040.SZ',\n",
       " '002041.SZ',\n",
       " '002042.SZ',\n",
       " '002043.SZ',\n",
       " '002044.SZ',\n",
       " '002045.SZ',\n",
       " '002046.SZ',\n",
       " '002047.SZ',\n",
       " '002048.SZ',\n",
       " '002049.SZ',\n",
       " '002050.SZ',\n",
       " '002051.SZ',\n",
       " '002052.SZ',\n",
       " '002053.SZ',\n",
       " '002054.SZ',\n",
       " '002055.SZ',\n",
       " '002056.SZ',\n",
       " '002057.SZ',\n",
       " '002058.SZ',\n",
       " '002059.SZ',\n",
       " '002060.SZ',\n",
       " '002061.SZ',\n",
       " '002062.SZ',\n",
       " '002063.SZ',\n",
       " '002064.SZ',\n",
       " '002065.SZ',\n",
       " '002066.SZ',\n",
       " '002067.SZ',\n",
       " '002068.SZ',\n",
       " '002069.SZ',\n",
       " '002072.SZ',\n",
       " '002073.SZ',\n",
       " '002074.SZ',\n",
       " '002075.SZ',\n",
       " '002076.SZ',\n",
       " '002077.SZ',\n",
       " '002078.SZ',\n",
       " '002079.SZ',\n",
       " '002080.SZ',\n",
       " '002081.SZ',\n",
       " '002082.SZ',\n",
       " '002083.SZ',\n",
       " '002084.SZ',\n",
       " '002085.SZ',\n",
       " '002086.SZ',\n",
       " '002088.SZ',\n",
       " '002090.SZ',\n",
       " '002091.SZ',\n",
       " '002092.SZ',\n",
       " '002093.SZ',\n",
       " '002094.SZ',\n",
       " '002095.SZ',\n",
       " '002096.SZ',\n",
       " '002097.SZ',\n",
       " '002098.SZ',\n",
       " '002099.SZ',\n",
       " '002100.SZ',\n",
       " '002101.SZ',\n",
       " '002102.SZ',\n",
       " '002103.SZ',\n",
       " '002104.SZ',\n",
       " '002105.SZ',\n",
       " '002106.SZ',\n",
       " '002107.SZ',\n",
       " '002108.SZ',\n",
       " '002109.SZ',\n",
       " '002110.SZ',\n",
       " '002111.SZ',\n",
       " '002112.SZ',\n",
       " '002114.SZ',\n",
       " '002115.SZ',\n",
       " '002116.SZ',\n",
       " '002117.SZ',\n",
       " '002119.SZ',\n",
       " '002120.SZ',\n",
       " '002121.SZ',\n",
       " '002122.SZ',\n",
       " '002123.SZ',\n",
       " '002124.SZ',\n",
       " '002125.SZ',\n",
       " '002126.SZ',\n",
       " '002127.SZ',\n",
       " '002128.SZ',\n",
       " '002129.SZ',\n",
       " '002130.SZ',\n",
       " '002131.SZ',\n",
       " '002132.SZ',\n",
       " '002133.SZ',\n",
       " '002134.SZ',\n",
       " '002135.SZ',\n",
       " '002136.SZ',\n",
       " '002137.SZ',\n",
       " '002138.SZ',\n",
       " '002139.SZ',\n",
       " '002140.SZ',\n",
       " '002141.SZ',\n",
       " '002142.SZ',\n",
       " '002144.SZ',\n",
       " '002145.SZ',\n",
       " '002146.SZ',\n",
       " '002148.SZ',\n",
       " '002149.SZ',\n",
       " '002150.SZ',\n",
       " '002151.SZ',\n",
       " '002152.SZ',\n",
       " '002153.SZ',\n",
       " '002154.SZ',\n",
       " '002155.SZ',\n",
       " '002156.SZ',\n",
       " '002157.SZ',\n",
       " '002158.SZ',\n",
       " '002159.SZ',\n",
       " '002160.SZ',\n",
       " '002161.SZ',\n",
       " '002162.SZ',\n",
       " '002163.SZ',\n",
       " '002164.SZ',\n",
       " '002165.SZ',\n",
       " '002166.SZ',\n",
       " '002167.SZ',\n",
       " '002168.SZ',\n",
       " '002169.SZ',\n",
       " '002170.SZ',\n",
       " '002171.SZ',\n",
       " '002172.SZ',\n",
       " '002173.SZ',\n",
       " '002174.SZ',\n",
       " '002175.SZ',\n",
       " '002176.SZ',\n",
       " '002177.SZ',\n",
       " '002178.SZ',\n",
       " '002179.SZ',\n",
       " '002180.SZ',\n",
       " '002181.SZ',\n",
       " '002182.SZ',\n",
       " '002183.SZ',\n",
       " '002184.SZ',\n",
       " '002185.SZ',\n",
       " '002186.SZ',\n",
       " '002187.SZ',\n",
       " '002188.SZ',\n",
       " '002189.SZ',\n",
       " '002190.SZ',\n",
       " '002191.SZ',\n",
       " '002192.SZ',\n",
       " '002193.SZ',\n",
       " '002194.SZ',\n",
       " '002195.SZ',\n",
       " '002196.SZ',\n",
       " '002197.SZ',\n",
       " '002198.SZ',\n",
       " '002199.SZ',\n",
       " '002200.SZ',\n",
       " '002201.SZ',\n",
       " '002202.SZ',\n",
       " '002203.SZ',\n",
       " '002204.SZ',\n",
       " '002205.SZ',\n",
       " '002206.SZ',\n",
       " '002207.SZ',\n",
       " '002208.SZ',\n",
       " '002209.SZ',\n",
       " '002210.SZ',\n",
       " '002211.SZ',\n",
       " '002212.SZ',\n",
       " '002213.SZ',\n",
       " '002214.SZ',\n",
       " '002215.SZ',\n",
       " '002216.SZ',\n",
       " '002217.SZ',\n",
       " '002218.SZ',\n",
       " '002219.SZ',\n",
       " '002221.SZ',\n",
       " '002222.SZ',\n",
       " '002223.SZ',\n",
       " '002224.SZ',\n",
       " '002225.SZ',\n",
       " '002226.SZ',\n",
       " '002227.SZ',\n",
       " '002228.SZ',\n",
       " '002229.SZ',\n",
       " '002230.SZ',\n",
       " '002231.SZ',\n",
       " '002232.SZ',\n",
       " '002233.SZ',\n",
       " '002234.SZ',\n",
       " '002235.SZ',\n",
       " '002236.SZ',\n",
       " '002237.SZ',\n",
       " '002238.SZ',\n",
       " '002239.SZ',\n",
       " '002240.SZ',\n",
       " '002241.SZ',\n",
       " '002242.SZ',\n",
       " '002243.SZ',\n",
       " '002244.SZ',\n",
       " '002245.SZ',\n",
       " '002246.SZ',\n",
       " '002247.SZ',\n",
       " '002248.SZ',\n",
       " '002249.SZ',\n",
       " '002250.SZ',\n",
       " '002251.SZ',\n",
       " '002252.SZ',\n",
       " '002253.SZ',\n",
       " '002254.SZ',\n",
       " '002255.SZ',\n",
       " '002256.SZ',\n",
       " '002258.SZ',\n",
       " '002259.SZ',\n",
       " '002261.SZ',\n",
       " '002262.SZ',\n",
       " '002263.SZ',\n",
       " '002264.SZ',\n",
       " '002265.SZ',\n",
       " '002266.SZ',\n",
       " '002267.SZ',\n",
       " '002268.SZ',\n",
       " '002269.SZ',\n",
       " '002270.SZ',\n",
       " '002271.SZ',\n",
       " '002272.SZ',\n",
       " '002273.SZ',\n",
       " '002274.SZ',\n",
       " '002275.SZ',\n",
       " '002276.SZ',\n",
       " '002277.SZ',\n",
       " '002278.SZ',\n",
       " '002279.SZ',\n",
       " '002281.SZ',\n",
       " '002282.SZ',\n",
       " '002283.SZ',\n",
       " '002284.SZ',\n",
       " '002285.SZ',\n",
       " '002286.SZ',\n",
       " '002287.SZ',\n",
       " '002289.SZ',\n",
       " '002290.SZ',\n",
       " '002291.SZ',\n",
       " '002292.SZ',\n",
       " '002293.SZ',\n",
       " '002294.SZ',\n",
       " '002295.SZ',\n",
       " '002296.SZ',\n",
       " '002297.SZ',\n",
       " '002298.SZ',\n",
       " '002299.SZ',\n",
       " '002300.SZ',\n",
       " '002301.SZ',\n",
       " '002302.SZ',\n",
       " '002303.SZ',\n",
       " '002304.SZ',\n",
       " '002305.SZ',\n",
       " '002306.SZ',\n",
       " '002307.SZ',\n",
       " '002309.SZ',\n",
       " '002310.SZ',\n",
       " '002311.SZ',\n",
       " '002312.SZ',\n",
       " '002313.SZ',\n",
       " '002314.SZ',\n",
       " '002315.SZ',\n",
       " '002316.SZ',\n",
       " '002317.SZ',\n",
       " '002318.SZ',\n",
       " '002319.SZ',\n",
       " '002320.SZ',\n",
       " '002321.SZ',\n",
       " '002322.SZ',\n",
       " '002323.SZ',\n",
       " '002324.SZ',\n",
       " '002326.SZ',\n",
       " '002327.SZ',\n",
       " '002328.SZ',\n",
       " '002329.SZ',\n",
       " '002330.SZ',\n",
       " '002331.SZ',\n",
       " '002332.SZ',\n",
       " '002333.SZ',\n",
       " '002334.SZ',\n",
       " '002335.SZ',\n",
       " '002336.SZ',\n",
       " '002337.SZ',\n",
       " '002338.SZ',\n",
       " '002339.SZ',\n",
       " '002340.SZ',\n",
       " '002342.SZ',\n",
       " '002343.SZ',\n",
       " '002344.SZ',\n",
       " '002345.SZ',\n",
       " '002346.SZ',\n",
       " '002347.SZ',\n",
       " '002348.SZ',\n",
       " '002349.SZ',\n",
       " '002350.SZ',\n",
       " '002351.SZ',\n",
       " '002352.SZ',\n",
       " '002353.SZ',\n",
       " '002354.SZ',\n",
       " '002355.SZ',\n",
       " '002356.SZ',\n",
       " '002357.SZ',\n",
       " '002358.SZ',\n",
       " '002360.SZ',\n",
       " '002361.SZ',\n",
       " '002362.SZ',\n",
       " '002363.SZ',\n",
       " '002364.SZ',\n",
       " '002365.SZ',\n",
       " '002366.SZ',\n",
       " '002367.SZ',\n",
       " '002368.SZ',\n",
       " '002369.SZ',\n",
       " '002370.SZ',\n",
       " '002371.SZ',\n",
       " '002372.SZ',\n",
       " '002373.SZ',\n",
       " '002374.SZ',\n",
       " '002375.SZ',\n",
       " '002376.SZ',\n",
       " '002377.SZ',\n",
       " '002378.SZ',\n",
       " '002379.SZ',\n",
       " '002380.SZ',\n",
       " '002381.SZ',\n",
       " '002382.SZ',\n",
       " '002383.SZ',\n",
       " '002384.SZ',\n",
       " '002385.SZ',\n",
       " '002386.SZ',\n",
       " '002387.SZ',\n",
       " '002388.SZ',\n",
       " '002389.SZ',\n",
       " '002390.SZ',\n",
       " '002391.SZ',\n",
       " '002392.SZ',\n",
       " '002393.SZ',\n",
       " '002394.SZ',\n",
       " '002395.SZ',\n",
       " '002396.SZ',\n",
       " '002397.SZ',\n",
       " '002398.SZ',\n",
       " '002399.SZ',\n",
       " '002400.SZ',\n",
       " '002401.SZ',\n",
       " '002402.SZ',\n",
       " '002403.SZ',\n",
       " '002404.SZ',\n",
       " '002405.SZ',\n",
       " '002406.SZ',\n",
       " '002407.SZ',\n",
       " '002408.SZ',\n",
       " '002409.SZ',\n",
       " '002410.SZ',\n",
       " '002412.SZ',\n",
       " '002413.SZ',\n",
       " '002414.SZ',\n",
       " '002415.SZ',\n",
       " '002416.SZ',\n",
       " '002418.SZ',\n",
       " '002419.SZ',\n",
       " '002420.SZ',\n",
       " '002421.SZ',\n",
       " '002422.SZ',\n",
       " '002423.SZ',\n",
       " '002424.SZ',\n",
       " '002425.SZ',\n",
       " '002426.SZ',\n",
       " '002427.SZ',\n",
       " '002428.SZ',\n",
       " '002429.SZ',\n",
       " '002430.SZ',\n",
       " '002431.SZ',\n",
       " '002432.SZ',\n",
       " '002434.SZ',\n",
       " '002436.SZ',\n",
       " '002437.SZ',\n",
       " '002438.SZ',\n",
       " '002439.SZ',\n",
       " '002440.SZ',\n",
       " '002441.SZ',\n",
       " '002442.SZ',\n",
       " '002443.SZ',\n",
       " '002444.SZ',\n",
       " '002445.SZ',\n",
       " '002446.SZ',\n",
       " '002448.SZ',\n",
       " '002449.SZ',\n",
       " '002451.SZ',\n",
       " '002452.SZ',\n",
       " '002453.SZ',\n",
       " '002454.SZ',\n",
       " '002455.SZ',\n",
       " '002456.SZ',\n",
       " '002457.SZ',\n",
       " '002458.SZ',\n",
       " '002459.SZ',\n",
       " '002460.SZ',\n",
       " '002461.SZ',\n",
       " '002462.SZ',\n",
       " '002463.SZ',\n",
       " '002465.SZ',\n",
       " '002466.SZ',\n",
       " '002467.SZ',\n",
       " '002468.SZ',\n",
       " '002469.SZ',\n",
       " '002470.SZ',\n",
       " '002471.SZ',\n",
       " '002472.SZ',\n",
       " '002474.SZ',\n",
       " '002475.SZ',\n",
       " '002476.SZ',\n",
       " '002478.SZ',\n",
       " '002479.SZ',\n",
       " '002480.SZ',\n",
       " '002481.SZ',\n",
       " '002482.SZ',\n",
       " '002483.SZ',\n",
       " '002484.SZ',\n",
       " '002485.SZ',\n",
       " '002486.SZ',\n",
       " '002487.SZ',\n",
       " '002488.SZ',\n",
       " '002489.SZ',\n",
       " '002490.SZ',\n",
       " '002491.SZ',\n",
       " '002492.SZ',\n",
       " '002493.SZ',\n",
       " '002494.SZ',\n",
       " '002495.SZ',\n",
       " '002496.SZ',\n",
       " '002497.SZ',\n",
       " '002498.SZ',\n",
       " '002500.SZ',\n",
       " '002501.SZ',\n",
       " '002506.SZ',\n",
       " '002507.SZ',\n",
       " '002508.SZ',\n",
       " '002510.SZ',\n",
       " '002511.SZ',\n",
       " '002512.SZ',\n",
       " '002513.SZ',\n",
       " '002514.SZ',\n",
       " '002515.SZ',\n",
       " '002516.SZ',\n",
       " '002517.SZ',\n",
       " ...]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stock_list = data['ts_code'].tolist()\n",
    "stock_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 000001.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\30136\\AppData\\Local\\Temp\\ipykernel_29588\\3169768863.py:9: UserWarning: The provided table name '000001.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='replace', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 000002.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\30136\\AppData\\Local\\Temp\\ipykernel_29588\\3169768863.py:9: UserWarning: The provided table name '000002.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='replace', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 000004.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\30136\\AppData\\Local\\Temp\\ipykernel_29588\\3169768863.py:9: UserWarning: The provided table name '000004.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='replace', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 000006.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\30136\\AppData\\Local\\Temp\\ipykernel_29588\\3169768863.py:9: UserWarning: The provided table name '000006.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='replace', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 000007.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\30136\\AppData\\Local\\Temp\\ipykernel_29588\\3169768863.py:9: UserWarning: The provided table name '000007.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='replace', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 000008.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\30136\\AppData\\Local\\Temp\\ipykernel_29588\\3169768863.py:9: UserWarning: The provided table name '000008.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='replace', index=False)\n"
     ]
    }
   ],
   "source": [
    "data_price = []\n",
    "i = 0\n",
    "for stock in data['ts_code'].to_list():\n",
    "    if i == 6:\n",
    "        break\n",
    "    i+=1\n",
    "    print(i,stock)\n",
    "    data_price = pro.daily(ts_code=stock)\n",
    "    data_price.to_sql(stock, con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   count(*)\n",
      "0      5395\n",
      "     ts_code  symbol  name area industry cnspell market list_date act_name  \\\n",
      "0  688698.SH  688698  伟创电气   江苏     电气设备    wcdq    科创板  20201229     None   \n",
      "\n",
      "  act_ent_type  \n",
      "0         None  \n"
     ]
    }
   ],
   "source": [
    "sql = \"select count(*) from stock_basic.stock_basic\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)\n",
    "sql = \"select * from stock_basic.stock_basic where name='伟创电气'\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
